package org.apogee.mil.server.admin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apogee.mil.client.services.admin.AdminService;
import org.apogee.mil.server.database.DatabaseDriver;
import org.apogee.mil.server.database.DatabaseDriver.TableStatus;
import org.apogee.mil.server.database.helper.DatabaseHelper;
import org.apogee.mil.server.database.helper.DatabaseQueries;
import org.apogee.mil.shared.Constants;
import org.apogee.mil.shared.Constants.AccessLevelFields;
import org.apogee.mil.shared.Constants.AllCdcFields;
import org.apogee.mil.shared.Constants.DisplayFormat;
import org.apogee.mil.shared.Constants.GroupFields;
import org.apogee.mil.shared.Constants.GroupTestLookupFields;
import org.apogee.mil.shared.Constants.GroupUserLookupFields;
import org.apogee.mil.shared.Constants.QuestionFields;
import org.apogee.mil.shared.Constants.TrainerTraineeLookupFields;
import org.apogee.mil.shared.Constants.UserLookupFields;
import org.apogee.mil.shared.Constants.UsersFields;
import org.apogee.mil.shared.Question;

import com.extjs.gxt.ui.client.data.BaseModel;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

public class AdminServiceImpl extends RemoteServiceServlet implements AdminService {

	private static Logger logger = Logger.getLogger(AdminServiceImpl.class);

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private DatabaseDriver db;

	@Override
	public boolean initialize() {
		db = new DatabaseDriver();
		return db.initialize();
	}

	@Override
	public boolean close() {
		try {
			return db.close();
		} catch (NullPointerException ex) {
			return false;
		}
	}

	@Override
	public boolean addUser(int userid, String username, String passwd,
			String firstName, String lastName, int accessLevel, String enabled) {
		int val = db.addUser(userid, username, passwd, firstName, lastName, accessLevel, enabled);

		if (val == -1) {
			logger.error("Unable to add User: " +username);
			return false;
		}
		logger.info("Successfully added: " +username);
		return true;
	}

	@Override
	public ArrayList<BaseModel> getAvailableTests() {
		ArrayList<BaseModel> model = new ArrayList<BaseModel>();

		String stmt = "select * from all_cdcs";

		ResultSet set = db.executeQuery(stmt);

		try {
			while(set.next()) {
				BaseModel bm = new BaseModel();
				bm.set(Constants.AllCdcFields.TEST_ID, set.getString(Constants.AllCdcFields.TEST_ID));
				bm.set(Constants.AllCdcFields.CDC, set.getString(Constants.AllCdcFields.CDC));
				bm.set(Constants.AllCdcFields.MIN_SCORE, set.getFloat(Constants.AllCdcFields.MIN_SCORE));
				bm.set(Constants.DisplayFormat.TEST_FMT, set.getString(Constants.AllCdcFields.TEST_ID) + " - " + set.getString(Constants.AllCdcFields.CDC));
				model.add(bm);
			}
		} catch (SQLException e) { }

		return model;
	}

	@Override
	public List<Question> getQuestions(String test, int numQuestions,
			int volume, int reviewType) {
		String stmt = DatabaseHelper.getQuestions(test, volume, reviewType);

		ResultSet set = db.executeQuery(stmt);

		List<Question> q = new ArrayList<Question>();

		try {
			while (set.next()) {
				ArrayList<String> choices = new ArrayList<String>();
				choices.add(set.getString(QuestionFields.CHOICE_1));
				choices.add(set.getString(QuestionFields.CHOICE_2));
				choices.add(set.getString(QuestionFields.CHOICE_3));
				choices.add(set.getString(QuestionFields.CHOICE_4));
				Question question = new Question();

				question.setQuestion(set.getString(QuestionFields.QUESTION));
				question.setChoices(choices);
				question.setAnswer(set.getInt(QuestionFields.ANSWER));
				question.setId(set.getInt(QuestionFields.ID));

				question.setVolume(set.getInt(QuestionFields.VOLUME));

				if (set.getString(QuestionFields.ENABLED).toLowerCase().equals("y")) {
					question.setEnabled(true);
				} else {
					question.setEnabled(false);
				}

				question.setType(set.getString(QuestionFields.REVIEW_OR_TEST));

				q.add(question);
			}
		} catch (SQLException e) {
			logger.error("Unable to get questions:\n" + e);
			return new ArrayList<Question>();
		}

		return q;
	}


	@Override
	public boolean updateQuestion(String test, Question question) {

		int val = db.addQuestion(test, question);

		if (val >= 0) 
			return true;

		return false;
	}

	@Override
	public ArrayList<BaseModel> getUsers(int accessLevel) {

		String stmt = String.format("SELECT u.%s, u.%s, ul.%s, ul.%s, u.%s, u.%s, a.%s, u.%s "+
				"FROM users u, user_lookup ul, access_level a "+
				"WHERE u.%s = ul.%s " +
				"AND a.%s = u.%s " +
				"AND a.%s >= %d", UsersFields.USER_ID, UsersFields.USER_NAME, 
				UserLookupFields.FIRST_NAME, UserLookupFields.LAST_NAME,
				UsersFields.PASSWORD, UsersFields.ACCESS_LEVEL,
				AccessLevelFields.DESCRIPTION, UsersFields.ENABLED, 
				UsersFields.USER_ID, UserLookupFields.USER_ID,
				AccessLevelFields.ID, UsersFields.ACCESS_LEVEL, AccessLevelFields.ID, accessLevel);

		ArrayList<BaseModel> users = new ArrayList<BaseModel>();

		ResultSet set = db.executeQuery(stmt);

		try {
			while(set.next()) {
				BaseModel bm = new BaseModel();

				String userId = set.getString(UsersFields.USER_ID);
				String firstName = set.getString(UserLookupFields.FIRST_NAME);
				String lastName = set.getString(UserLookupFields.LAST_NAME);


				bm.set(UsersFields.USER_ID, userId);
				bm.set(UsersFields.USER_NAME, set.getString(UsersFields.USER_NAME));
				bm.set(UserLookupFields.FIRST_NAME, firstName);
				bm.set(UserLookupFields.LAST_NAME, lastName);
				bm.set(UsersFields.PASSWORD, set.getString(UsersFields.PASSWORD));

				String access = set.getString(UsersFields.ACCESS_LEVEL);
				String description = set.getString(AccessLevelFields.DESCRIPTION);
				String accessLevelFmt = access + " - " + description;

				bm.set(UsersFields.ACCESS_LEVEL, access);
				bm.set(AccessLevelFields.DESCRIPTION, description);
				bm.set(DisplayFormat.ACCESS_FMT, accessLevelFmt);

				bm.set(DisplayFormat.USER_FMT, userId + " - " + firstName + " " + lastName);

				bm.set(UsersFields.ENABLED, set.getString(UsersFields.ENABLED).toLowerCase().equals("y") ? true : false);
				users.add(bm);

			}
		} catch (SQLException ex){ 
			logger.error(ex.getMessage());
		}

		return users;
	}

	@Override
	public ArrayList<BaseModel> getAccessLevels() {
		String stmt = String.format("select * from access_level order by %s desc", AccessLevelFields.ID);

		ArrayList<BaseModel> levels = new ArrayList<BaseModel>();

		ResultSet set = db.executeQuery(stmt);

		try {
			while(set.next()) {
				BaseModel bm = new BaseModel();
				String id = set.getString(AccessLevelFields.ID);
				String desc = set.getString(AccessLevelFields.DESCRIPTION);
				String descFmt = id + " - " + desc;
				bm.set(AccessLevelFields.ID, id);
				bm.set(AccessLevelFields.DESCRIPTION, desc);
				bm.set(DisplayFormat.DESCRIPTION_FMT, descFmt);
				levels.add(bm);
			}
		} catch (SQLException ex) { }

		return levels;
	}

	@Override
	public boolean updateUsers(ArrayList<BaseModel> changes) {
		int val = 0;

		for (BaseModel bm : changes) {
			boolean newUser = bm.get(DisplayFormat.NEW) == null ? false : true;
			int id = Integer.valueOf(bm.get(UsersFields.USER_ID).toString());
			String user = bm.get(UsersFields.USER_NAME);
			String pwd = bm.get(UsersFields.PASSWORD);

			String firstName = bm.get(UserLookupFields.FIRST_NAME);
			String lastName = bm.get(UserLookupFields.LAST_NAME);

			int accessLevel = Integer.valueOf(bm.get(AccessLevelFields.ID).toString());

			String enabled = Boolean.valueOf(bm.get(UsersFields.ENABLED).toString()) == true ? "Y" : "N";

			if (newUser) {
				val += db.addUser(id, user, pwd, firstName, lastName, accessLevel, enabled);
			} else {
				val += db.updateUser(id, user, pwd, firstName, lastName, accessLevel, enabled);
			}

		}


		if (val == -1) {
			logger.error("Unable to add Users");
			return false;
		}
		logger.info("Successfully updated users");
		return true;
	}

	@Override
	public ArrayList<BaseModel> getGroups() {
		String stmt = "select * from groups";

		ArrayList<BaseModel> groups = new ArrayList<BaseModel>();

		ResultSet set = db.executeQuery(stmt);

		try {
			while (set.next()) {
				BaseModel bm = new BaseModel();
				bm.set(GroupFields.GROUP_ID, set.getString(GroupFields.GROUP_ID));
				bm.set(GroupFields.GROUP_NAME, set.getString(GroupFields.GROUP_NAME));

				String groupNameFmt = set.getString(GroupFields.GROUP_ID) + " - " + set.getString((GroupFields.GROUP_NAME));

				bm.set(DisplayFormat.GROUP_NAME_FMT, groupNameFmt);

				groups.add(bm);
			}
		} catch (SQLException ex) {
			logger.error(ex);
		}

		return groups;
	}

	@Override
	public boolean updateGroups(ArrayList<BaseModel> changed) {

		int val = 0;

		for (BaseModel bm : changed) {
			boolean newGroup = bm.get(DisplayFormat.NEW) == null ? false : true;
			String groupId = bm.get(GroupFields.GROUP_ID);
			String groupName = bm.get(GroupFields.GROUP_NAME);

			if (bm.get(DisplayFormat.DELETE) != null && Boolean.parseBoolean(bm.get(DisplayFormat.DELETE).toString()) == true) {
				boolean result = db.executeUpdate(String.format("delete from groups where %s=%s",GroupFields.GROUP_ID, groupId));
				if (result) {
					val += 1;
				}
			} else if (newGroup) {
				val += db.insertGroup(groupId, groupName);
			} else {
				val += db.updateGroup(groupId, groupName);
			}
		}

		if (val < 1) {
			return false;
		}

		return true;
	}

	@Override
	public ArrayList<BaseModel> getTestsFromGroup(String groupId) {
		String stmt = String.format("SELECT a.%s, a.%s, b.%s " +
				"FROM group_test_lookup a, all_cdcs b "+
				"WHERE a.%s = b.%s " +
				"AND a.%s=%s", GroupTestLookupFields.TEST_ID, GroupTestLookupFields.GROUP_ID,
				AllCdcFields.CDC, GroupTestLookupFields.TEST_ID,
				AllCdcFields.TEST_ID, GroupTestLookupFields.GROUP_ID, groupId);

		ArrayList<BaseModel> tests = new ArrayList<BaseModel>();

		ResultSet set = db.executeQuery(stmt);

		try { 
			while (set.next()) {
				BaseModel bm = new BaseModel();

				bm.set(GroupTestLookupFields.TEST_ID, set.getString(GroupTestLookupFields.TEST_ID));
				bm.set(GroupTestLookupFields.GROUP_ID, set.getString(GroupTestLookupFields.GROUP_ID));
				bm.set(AllCdcFields.CDC, set.getString(AllCdcFields.CDC));
				bm.set(DisplayFormat.TEST_FMT, set.getString(GroupTestLookupFields.TEST_ID) + " - " + set.getString(AllCdcFields.CDC));

				tests.add(bm);

			}
		} catch (SQLException ex){ 
			logger.error(ex);
		}

		return tests;
	}

	@Override
	public ArrayList<BaseModel> getUsersFromGroup(String groupId) {
		String stmt = String.format("SELECT u.%s, u.%s, ul.%s, ul.%s, g.%s "+
				"FROM users u, user_lookup ul, group_user_lookup g "+
				"WHERE u.%s = ul.%s " +
				"AND u.%s = g.%s " +
				"AND g.%s=%s", UsersFields.USER_ID, UsersFields.USER_NAME, UserLookupFields.FIRST_NAME, 
				UserLookupFields.LAST_NAME, GroupUserLookupFields.GROUP_ID,
				UsersFields.USER_ID, GroupUserLookupFields.USER_ID,
				UsersFields.USER_ID, UserLookupFields.USER_ID,
				GroupUserLookupFields.GROUP_ID, groupId);

		ArrayList<BaseModel> users = new ArrayList<BaseModel>();

		ResultSet set = db.executeQuery(stmt);

		try { 
			while (set.next()) {
				BaseModel bm = new BaseModel();

				String userId = set.getString(UsersFields.USER_ID);
				String firstName = set.getString(UserLookupFields.FIRST_NAME);
				String lastName = set.getString(UserLookupFields.LAST_NAME);

				bm.set(UsersFields.USER_ID, userId);
				bm.set(UsersFields.USER_NAME, set.getString(UsersFields.USER_NAME));
				bm.set(UserLookupFields.FIRST_NAME, firstName);
				bm.set(UserLookupFields.LAST_NAME, lastName);
				bm.set(GroupUserLookupFields.GROUP_ID, set.getString(GroupUserLookupFields.GROUP_ID));

				bm.set(DisplayFormat.USER_FMT, userId + " - " + firstName + " " + lastName);


				users.add(bm);

			}
		} catch (SQLException ex){ 
			logger.error(ex);
		}

		return users;
	}

	@Override
	public boolean updateGroupings(String groupId, ArrayList<BaseModel> changes) {
		int retVal = 0;

		for(BaseModel bm : changes) {
			if (bm.get(DisplayFormat.TEST_CHANGES) != null) {
				if (bm.get(DisplayFormat.TEST_FMT) != null) {
					String testFmt = bm.get(DisplayFormat.TEST_FMT);
					String testId = testFmt.toString().substring(0, testFmt.indexOf("-")).trim();

					if (bm.get(DisplayFormat.DELETE) != null && Boolean.parseBoolean(bm.get(DisplayFormat.DELETE).toString()) == true) {
						boolean result = db.executeUpdate(String.format("delete from group_test_lookup where %s=%s", GroupTestLookupFields.TEST_ID, testId));
						if (result) {
							retVal += 1;
						}
					} else {
						retVal += db.insertGroupTest(groupId, testId);
					}
				}
			} else if (bm.get(DisplayFormat.USER_CHANGES) != null) {
				if (bm.get(DisplayFormat.USER_FMT) != null) {
					String userFmt = bm.get(DisplayFormat.USER_FMT);
					String userId = userFmt.toString().substring(0, userFmt.indexOf("-")).trim();

					if (bm.get(DisplayFormat.DELETE) != null && Boolean.parseBoolean(bm.get(DisplayFormat.DELETE).toString()) == true) {
						boolean result = db.executeUpdate(String.format("delete from group_user_lookup where %s=%s", GroupUserLookupFields.USER_ID, userId));
						if (result) {
							retVal += 1;
						}
					} else {
						retVal += db.insertGroupUser(groupId, userId);
					}
				}
			}
		}

		if (retVal > 0) {
			return true;
		}

		return false;
	}

	@Override
	public boolean createTest(String testName) {
		TableStatus status = db.createDatabase(testName);

		if (status == TableStatus.ADDED) {
			return true;
		}

		return false;
	}

	@Override
	public ArrayList<BaseModel> getTrainers() {
		String stmt = String.format("SELECT u.%s, u.%s, ul.%s, ul.%s "+
				"FROM users u, user_lookup ul, access_level a "+
				"WHERE u.%s = ul.%s " +
				"AND a.%s = u.%s " +
				"AND a.%s < 3", UsersFields.USER_ID, UsersFields.USER_NAME, UserLookupFields.FIRST_NAME,
				UserLookupFields.LAST_NAME, UsersFields.USER_ID, UserLookupFields.USER_ID,
				AccessLevelFields.ID, UsersFields.ACCESS_LEVEL, AccessLevelFields.ID);

		ArrayList<BaseModel> users = new ArrayList<BaseModel>();

		ResultSet set = db.executeQuery(stmt);

		try {
			while (set.next()) {
				BaseModel bm = new BaseModel();

				String userId = set.getString(UsersFields.USER_ID);
				String firstName = set.getString(UserLookupFields.FIRST_NAME);
				String lastName = set.getString(UserLookupFields.LAST_NAME);

				String nameFmt = userId + " - " + firstName + " " + lastName;

				bm.set(UsersFields.USER_ID, userId);
				bm.set(UsersFields.USER_NAME, set.getString(UsersFields.USER_NAME));
				bm.set(UserLookupFields.FIRST_NAME, firstName);
				bm.set(UserLookupFields.LAST_NAME, lastName);
				bm.set(DisplayFormat.NAME_FMT, nameFmt);

				users.add(bm);

			}
		} catch (SQLException e) {
			logger.error(e);
		}

		return users;
	}


	@Override
	public boolean updateTrainer(int trainerId, ArrayList<BaseModel> changes) {

		boolean good = true;

		for (BaseModel bm : changes) {

			boolean delete = bm.get(DisplayFormat.DELETE) == null ? false : 
				(bm.get(DisplayFormat.DELETE).toString().toLowerCase().equals("true") ? true : false);

			String trainee = bm.get(DisplayFormat.USER_FMT);
			String traineeId = trainee.substring(0, trainee.indexOf("-")).trim();

			if (delete) {
				String stmt = String.format("select count(*) from trainer_trainee_lookup where %s=%d and %s=%s", 
						TrainerTraineeLookupFields.TRAINER_ID, trainerId, 
						TrainerTraineeLookupFields.TRAINEE_ID, traineeId);

				ResultSet set = db.executeQuery(stmt);

				int count = 0;

				try {
					set.next();
					count = Integer.parseInt(set.getString(1));
				} catch (Exception e) {	}

				if (count > 0) {
					stmt = String.format("delete from trainer_trainee_lookup where %s=%d and %s=%s", 
							TrainerTraineeLookupFields.TRAINER_ID, trainerId, 
							TrainerTraineeLookupFields.TRAINEE_ID, traineeId);

					good = good & db.executeUpdate(stmt);
				}

			} else {

				String[] fields = TrainerTraineeLookupFields.TRAINER_TRAINEE_LOOKUP_FIELDS;
				String[] values = {"", String.valueOf(trainerId), traineeId};

				String stmt = DatabaseHelper.createInsertStatement("trainer_trainee_lookup",
						fields, values);


				good = good & db.executeUpdate(stmt);
			}

		}


		return good;
	}

	@Override
	public ArrayList<BaseModel> getAllUsers() {
		ArrayList<BaseModel> list = new ArrayList<>();
		
		String stmt = String.format(DatabaseQueries.GET_USERS_BY_ACCESS_LEVEL, UsersFields.USER_ID,
				UserLookupFields.FIRST_NAME, UserLookupFields.LAST_NAME, UserLookupFields.USER_ID, 
				UsersFields.USER_ID, UsersFields.ACCESS_LEVEL, 3);
		
		ResultSet set = db.executeQuery(stmt);
		
		try {
			while (set.next()) {
				BaseModel bm = new BaseModel();

				String userId = set.getString(UsersFields.USER_ID);
				String firstName = set.getString(UserLookupFields.FIRST_NAME);
				String lastName = set.getString(UserLookupFields.LAST_NAME);

				String nameFmt = userId + " - " + firstName + " " + lastName;

				bm.set(UsersFields.USER_ID, userId);
				bm.set(UsersFields.USER_NAME, set.getString(UsersFields.USER_NAME));
				bm.set(UserLookupFields.FIRST_NAME, firstName);
				bm.set(UserLookupFields.LAST_NAME, lastName);
				bm.set(DisplayFormat.NAME_FMT, nameFmt);
				
				list.add(bm);
			}
		} catch (SQLException ex) {
			logger.error("Unable to execute Query: " + stmt, ex);
		}
		
		return list;
	}

}
